//1. set this to the desired path
cd "~/Desktop/LAP_PSRM_Replication/"

//2. Import the core replication file
use "LAP_Replication_COOHWIUS.dta", clear

//3. Limit the sample to the key years
drop if year > 2016
drop if year > 2014 & state == "wi"

//4. Find which bills have votes on them
gen votes = . 
replace votes = 1 if party_diff != . 

//5. Reduce each bill to a single line
collapse (sum) votes (min) year, by(orgs* bill_id session state)

//6. Only keep the bills that had a passage vote
drop if votes == 0

//7. Calculate how many bills and votes, and the average number of groups lobbying on a bill in each session by state. 
gen bills = 1 
egen average_groups = rowtotal(orgs*)
collapse (sum) votes bills (mean) average_g (min) year, by(state sess)

//8. The following code helps the data fit into a latex table
forvalues i= 1/5{
gen amp`i' = "&"
}
format aver %12.1f
format bills votes %12.0fc
gen ender = "\\"
order state amp1 sess amp2 year amp3 bills amp4 votes amp5 aver ender

//9. Change the labels on the data.
replace session = session+"th" if state == "us" | state == "oh"
//drop the repetitive state labels
egen counter = rank(year), by(state) unique
replace state = "" if counter > 1
drop counter
//clean up the labels
replace session = subinstr(session,"Regular Session","Reg.",.)

//10. Create Table 1
list state session year bills votes average_groups
